import datetime
import logging
import pymysql
from insert_sql import insert_etl_db

logging.basicConfig(level=logging.INFO, format='%(asctime)s %(filename)s[line:%(lineno)d] %(levelname)s %(message)s',
                    datefmt='%a, %d %b %Y %H:%M:%S')

class cpa_etl_db:

    def __init__(self):

        # palo数据库
        self.host = "ip"
        self.port = 110
        self.user = "user"
        self.passwd = "passed"
        self.db = "db"
        # 爬虫数据库
        self.insert_host = "ip"
        self.insert_port = 110
        self.insert_user = "user"
        self.insert_passwd = "pwd"
        self.insert_db = "db"
        self.yesterday = (datetime.datetime.now() + datetime.timedelta(days=-1)).strftime('%Y%m%d')
        self.select_sql = "select time_for_resp,daily_count from tb_daily_info where time_for_resp = '{}' and channel_id = 'toufang_cpa'".format(self.yesterday)

    def select_mysql(self, exec_sql):

        db = pymysql.connect(host=self.host, port=self.port, user=self.user, passwd=self.passwd, db=self.db)
        # 通过cursor创建游标
        cursor = db.cursor()
        try:
            cursor.execute(exec_sql)
            channel_id_tuple = cursor.fetchall()[0]
            data_and_daily_count = channel_id_tuple

            # 提交SQL
            db.commit()
        except Exception as e:
            print(datetime.datetime.now(), e)
            # 发生错误时回滚
            db.rollback()
            # 关闭数据库连接
        finally:
            db.close()
        return data_and_daily_count[1]

    def exec_sum(self):
        exec_sql = self.select_sql
        return int(self.select_mysql(exec_sql)*15000)

def cpa():

    if cpa_etl_db().exec_sum():
        insert_etl_db(str(cpa_etl_db().exec_sum()), 'toufang_cpa', 'cpa').insert_mysql()


if __name__ == '__main__':

    print (cpa_etl_db().exec_sum())


